Using APIs for Database Access

This topic describes the procedure to use APIs for database access.

Before you begin this task:
All database requests in Process Platform need to follow a standard protocol. The XQY API is a component in Process Platform that handles the Process Platform database protocol. This XQY API is accessible to a Java classcom.eibus.applicationconnector.sql.DBConnectionPool. The DBConnectionPool represents a pool of connections to the database via XQY APi. This pool exposes different API's to route requests to the database. This allows the user to contact the database directly in Process Platform environment. It also decreases the response time and allows users to perform dynamic query execution.

The public class DBConnectionPool can be used for direct database access from Process Platform. This Java class allows the user to directly send database requests to and receive responses from the XQY API of the database, instead of routing the requests as SOAP messages via application connectors.

Create a connection pool. The public Java class DBConnectionPool present in the com.eibus.applicationconnector.sql package can be used to create a connection pool by supplying all the details required to connect to the DB server in XML form.

String dsoXml = null;
dsoXml = <configuration>
    <update-connections>10</update-connections>
    <read-connections>10</read-connections>
    <dso provider="SQLNCLI"
            jdbcDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           driver="OLEDB"
           connectionString="jdbc:sqlserver://srv-ind-intdb:1433"
           defaultDB="TEST_XQY"
           dataSource="SRV-IND-BCP3"
           userId="sa"
           xmlencoding="false"
           password="YzByZHlz"
           update="true" >
        <query-cache>
            <size>50</size>
            <refresh-interval>3600</refresh-interval>
        </query-cache>
        <cursor-cache>
            <size>50</size>
            <refresh-interval>3600</refresh-interval>
        </cursor-cache>
    </dso>
    <connection-pool>
        <min-update-connections>1</min-update-connections>
        <min-read-connections>1</min-read-connections>
        <refresh-interval>3600</refresh-interval>
    </connection-pool>
</configuration>";
int dso = xmlDoc.parseString(dsoXml);
DBConnectionPool pool = DBConnectionPool._createInstance(dso, null, null, null);

This will create a read and an update connection in the pool. For information about createInstance, refer to Java SDK documentation.

Compose the database request. The database request, as required, can be composed using the following Web service Operations:

Method	Description
query	Reads data from the database
update	Updates data in the database
getMetaData	Retrieves the metadata of the database
validateCommand	Validates a query against the database
commitCurrentTransaction	Commits the current transaction to the database
abortCurrentTransaction	Aborts the current transaction
executeDDL	Executes the input DDL against the database

To query the database:

<dataset>
    <constructor language="DBSQL">
        <query>select * from Employees where EmployeeID = :EmployeeID</query>
        <parameters>
            <EmployeeID dd="Employees.EmployeeID">1</EmployeeID>
        </parameters>
    </constructor>
</dataset>

To retrieve the metadata of the database:

<dataset>
    <metadata requestType="getTableInfo" tableCatalog="Northwind"
        tableName="Employees" tableSchema="" tableType="TABLE"/>
</dataset>

To validate a query against the database:

<implementation>
    <validate>
        <query> select * from Employees where EmployeeID = :EmployeeID</query>
    </validate>
</implementation>

To execute an input DDL against the database:

<implementation>
    <executeDDL>create table temp_table (field1 int primary key, field2 varchar(20))</executeDDL>
</implementation>

To update the database:

<update>
    <tuple>
        <old>
            <Employees>
                <EmployeeID>1</EmployeeID>
                <FirstName>Nancy</FirstName>
            </Employees>
        </old>
        <new>
            <Employees>
                <EmployeeID>1</EmployeeID>
                <FirstName>Nancy123</FirstName>
            </Employees>
        </new>
    </tuple>
</update>

To execute an input DML against the database:

<implementation>
    <executeDML>
        <DML>
            <command> INSERT INTO Employees(FirstName, LastName, Title, City) values(:FirstName, :LastName, :Title, :City) </command>
            <parameters>
                <FirstName dd="Employees.FirstName">Test</FirstName>
                <LastName dd="Employees.LastName">Test</LastName>
                <Title dd="Employees.Title">Test</Title>
                <City dd="Employees.City">Test</City>
            </parameters>
        </DML>
    </executeDML>
</implementation>

Get an appropriate connection from the pool. The connection pool contains both read and write connections. Read connections can be used to process requests like query, metadata, and validate. Write connections can be used to process requests like update records, DDL request, and DML requests where transaction is necessary. Based on the request to be processed, applications can use appropriate APIs to get a read or updateconnection.
```
WCPDBConnection con = null;
con = pool.getReadConnection();
OR
con = pool.getWriteConnection();
```

Using appropriate APIs of WCPDBConnection class, send a request to the DB Server. This class provides the following APIs to execute the request.

Method	Description
query	Reads data from the database.Pass the <constructor> node xml to query API
update	Updates data in the database
getMetaData	Retrieves the metadata of the database
validateCommand	Validates a query against the database
commitCurrentTransaction	Commits the current transaction to the database
abortCurrentTransaction	Aborts the current transaction
executeDDL	Executes the input DDL against the database
executeDML	Executes the input DML against the database
createAuditTable	Creates an audit table from an existing table
purgeAuditTable	Removes the contents of audit table

Analyze the return value of the database response. Once the database requests are sent, you will receive database responses. The database responses are in XML format. On the basis of the return value, you can determine whether a request is processed successfully or an error occurred. If the return value is 0, then the request is processed successfully. If it is less than zero, then the request execution resulted in an error. Based on the return value, the application can either commit or abort the transaction for the operation that needs transaction support.

If(ret <0)
{
// error happened
con.abortTransaction(); // for write connections
}
else
{
// Processing is success
con.commitTransaction();
}

Given below are the sample responses received in case of success and in case of failure:

 Success
<dataset>
    <constructor language="DBSQL">
        <query>select EmployeeID, FirstName, LastName from Employees where EmployeeID = :EmployeeID</query>
        <parameters>
            <EmployeeID
            dd="Employees.EmployeeID">1</EmployeeID>
        </parameters>
    </constructor>
    <tuple>
        <old>
            <Employees>
                <EmployeeID>1</EmployeeID>
                <FirstName>Nancy123</FirstName>
                <LastName>Davolio</LastName>
            </Employees>
        </old>
    </tuple>
</dataset>
 
Error
<dataset>
    <constructor language="DBSQL">
        <query>select EmployeeID, FirstName, LastName1 from Employees where EmployeeID = :EmployeeID</query>
        <parameters>
           <EmployeeID
            dd="Employees.EmployeeID">1</EmployeeID>
        </parameters>
    </constructor>
    <error
        TYPE="Enumeration">
        <elem>ColumnsRowset formatting problem</elem>
        <elem>Failed to setQueryMetaData()</elem>
    </error>
</dataset>

Place the connection back into the pool. After the processing is done, it is mandatory to place the connection back into the pool. Otherwise, this connection cannot be used for any other request processing. You can do this by using Web service Operations provided in DBConnectionPool class.
```
Pool.putReadConnection(con); OR Pool.putWriteConnection(con);
```

pp108 : Using APIs for Database Access

Using APIs for Database Access